USE [texchange_db]
GO

/****** Object:  UserDefinedFunction [dbo].[Fn_GetAllCompany]    Script Date: 05/29/2011 19:53:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[Fn_GetAllCompany] 
(	
	@lang bigint,
	@start  bigint=0,
	@limit bigint=30,
	@order nvarchar(10) = 'id',
	@orderdir nvarchar(4) = 'desc',
	@name nvarchar(50) = null,
	@catid int = null,
	@ttype int = null
	-- Add the parameters for the function here
)
RETURNS @Company TABLE(
	[id] bigint,
	[name] nvarchar(100), [country_id] bigint, created datetime,
	[country_name] nvarchar(100), [City] nvarchar(150), 
	[operating_activity] nvarchar(200),
	[type_company_id] bigint,
	[companyType] nvarchar(50), member_type_id int
) 
AS
BEGIN 
	Declare @SQLQuery AS NVarchar(4000)
	SET @SQLQuery = 'SELECT id, name, country_id, created, country_name, City, operating_activity, type_company_id,companyType, member_type_id FROM 
			(SELECT Company.id, Company.name, Company.country_id, Company.created, Places.name as country_name,  
			Company.City, Company.operating_activity, Company.type_company_id, UniversalType.name as companyType,  
			Company.member_type_id, ROW_NUMBER() OVER (ORDER BY Company.id asc) AS rownum FROM Company 
			LEFT JOIN Places ON Company.country_id = Places.id '
	IF @lang = 1
	BEGIN
		SET @SQLQuery = @SQLQuery + ' LEFT JOIN UniversalType ON Company.type_company_id = UniversalType.id '
	END
	ELSE
	BEGIN
		SET @SQLQuery = @SQLQuery + ' LEFT JOIN UniversalType ON (Company.type_company_id = UniversalType.parent_id AND UniversalType.language_id=@lang) '
	END
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN CJCompanyTransport ON Company.id=CJCompanyTransport.company_id WHERE 1=1'
	
	IF @catid Is Not Null
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Company.type_company_id=@catid '
	END
	IF @name Is Not Null
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Company.name LIKE %@name% '
	END
	IF @ttype Is Not Null
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND CJCompanyTransport.transporttype_id = @ttype '
	END
	
	SET @SQLQuery = @SQLQuery + ') as a WHERE rownum >=@start AND rownum<=@limit'
	Declare @ParamDefinition AS NVarchar(2000) 
	Set @ParamDefinition = ' @lang bigint,
			@start  bigint=0,
			@limit bigint=30,
			@order nvarchar(10),
			@orderdir nvarchar(4),
			@name nvarchar(50),
			@catid int = null,
			@ttype int = null'
	--PRINT @SQLQuery
	--INSERT INTO @Company
	Execute sp_Executesql @SQLQuery, @ParamDefinition,@lang,@start,@limit,@order,@orderdir,@name,@catid,@ttype
	RETURN
END

GO


